package org.hope.db.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hope.db.ConnectionManager;
import org.hope.db.RelationalSearcher;
import org.hope.result.City;
import org.hope.result.Hotel;
import org.hope.result.Result;

/**
 * relational database searcher
 * 
 * @author Thilanka
 *
 */
public class MySqlSearcher extends RelationalSearcher
{

	@Override
	public List<Result> search( List<City> locations )
	{
		List<Result> hotels = new ArrayList<Result>();

		// if there are no locations, no need to search
		if(locations.size()==0)
		{
			return hotels;
		}
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		StringBuilder cityNameList = new StringBuilder();
		for ( City city : locations )
		{
			cityNameList.append( "'" + city.getName() + "'" ).append( "," );
		}
		if ( cityNameList.length() > 0 )
		{
			cityNameList.deleteCharAt( cityNameList.lastIndexOf( "," ) );
		}

		String query = "SELECT * FROM HOTEL WHERE CITY_NAME IN (" + cityNameList.toString() + ")";
		try
		{
			con = ConnectionManager.getConnection();

			stmt = con.prepareStatement( query );
			rs = stmt.executeQuery();

			while ( rs.next() )
			{
				String hotelName = rs.getString( "NAME" );
				String cityName = rs.getString( "CITY_NAME" );

				Hotel hotel = new Hotel();
				hotel.setName( hotelName );
				hotel.setCityName( cityName );

				hotels.add( hotel );
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace();
			return null;
		}
		finally
		{
			ConnectionManager.close( rs );
			ConnectionManager.close( stmt );
			ConnectionManager.close( con );
		}

		return hotels;
	}

	@Override
	public void fillDetails( List<Result> results )
	{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		Map<String, Result> hotelMap = new HashMap<String, Result>();
		
		StringBuilder hotelNameList = new StringBuilder();
		for ( Result result : results )
		{
			hotelNameList.append( "'" + result.getName() + "'" ).append( "," );
			hotelMap.put( result.getName(), result );
		}
		if ( hotelNameList.length() > 0 )
		{
			hotelNameList.deleteCharAt( hotelNameList.lastIndexOf( "," ) );
		}

		String query = "SELECT * FROM HOTEL WHERE NAME IN (" + hotelNameList.toString() + ")";
		try
		{
			con = ConnectionManager.getConnection();

			stmt = con.prepareStatement( query );
			rs = stmt.executeQuery();

			while ( rs.next() )
			{
				String hotelName = rs.getString( "NAME" );
				String cityName = rs.getString( "CITY_NAME" );
				
				// get the hotel from the map
				Hotel matchedHotel = (Hotel)hotelMap.get( hotelName );
				matchedHotel.setCityName( cityName );
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace();
		}
		finally
		{
			ConnectionManager.close( rs );
			ConnectionManager.close( stmt );
			ConnectionManager.close( con );
		}
	}

}
